From 7dd0cf2daa302d8534f07ebb498856783f1e3784 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 22 Jul 2026 22:37:43 +0000 Subject: [PATCH] Allow DYADS to UPDATE and DELETE --- db/schemas/lib/triggers/create/dyads.m4 | 135 +++++++++++++++++++++++- doc/src/views/dyads.m4 | 28 ++++- 2 files changed, 159 insertions(+), 4 deletions(-) diff --git a/db/schemas/lib/triggers/create/dyads.m4 b/db/schemas/lib/triggers/create/dyads.m4 index a345056..057f658 100644 --- a/db/schemas/lib/triggers/create/dyads.m4 +++ b/db/schemas/lib/triggers/create/dyads.m4 @@ -523,6 +523,137 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () $$; +RAISE INFO 'dyads_update_func'; +CREATE OR REPLACE FUNCTION dyads_update_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for dyads instead of update trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + -- + -- Remarks: + -- Do not enforce any conditions on the ID values. The place + -- to do that is in the triggers on the tables, so the rules are + -- consistent no matter the interface used to change table content. + + -- Style must be valid + IF NEW.style <> 'sdb_directed_style' + AND NEW.style <> 'sdb_mutual_style' + AND NEW.style <> 'sdb_unk_style' THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on UPDATE of DYADS' + , DETAIL = 'Invalid DYADS.Style value (' + || NEW.style + || ') : ' + || _show_row() + , HINT = 'The allowed values are:' + || ' sdb_directed_style, sdb_mutual_style, and sdb_unk_style'; + END IF; + + -- ROLES can't be updated, so there's no point in doing this. + -- But leave the code here, commented out, just in case. +-- -- Update Actor +-- UPDATE roles +-- SET pid = NEW.actor_pid +-- , eid = NEW.eid +-- , role = CASE +-- WHEN NEW.style = 'sdb_directed_style' THEN +-- 'sdb_actor' +-- WHEN NEW.style = 'sdb_mutual_style' THEN +-- 'sdb_mutual' +-- ELSE -- NEW.style = 'sdb_unk_style' THEN +-- 'sdb_unkpair' +-- END +-- , participant = NEW.actor +-- WHERE roles.pid = OLD.actor_pid; +-- +-- -- Update Recipient +-- UPDATE roles +-- SET pid = NEW.recipient_pid +-- , eid = NEW.eid +-- , role = CASE +-- WHEN NEW.style = 'sdb_directed_style' THEN +-- 'sdb_actee' +-- WHEN NEW.style = 'sdb_mutual_style' THEN +-- 'sdb_mutual' +-- ELSE -- NEW.style = 'sdb_unk_style' THEN +-- 'sdb_unkpair' +-- END +-- , participant = NEW.recipient +-- WHERE roles.pid = OLD.recipient_pid; + + -- Update EVENTS + UPDATE events + SET eid = NEW.eid + , wid = NEW.wid + , behavior = NEW.behavior + , start = NEW.start + , stop = NEW.stop + , certainty = NEW.certainty + , notes = NEW.event_notes + WHERE events.eid = OLD.eid; + + -- Update WATCHES + UPDATE watches + SET wid = NEW.wid + , animid = NEW.animid + , commid = NEW.commid + , date = NEW.date + , type = NEW.type + , notes = NEW.notes + WHERE watches.wid = OLD.wid; + + RETURN NEW; + END; +$$; + + +RAISE INFO 'dyads_delete_func'; +CREATE OR REPLACE FUNCTION dyads_delete_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for dyads instead of delete trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + -- + + DELETE FROM roles + WHERE roles.pid = OLD.actor_pid + OR roles.pid = OLD.recipient_pid; + + IF EXISTS + (SELECT 1 + FROM roles + WHERE roles.eid = OLD.eid) THEN + RETURN OLD; + END IF; + + DELETE FROM events + WHERE events.eid = OLD.eid; + + IF EXISTS + (SELECT 1 + FROM events + WHERE events.wid = OLD.wid) THEN + RETURN OLD; + END IF; + + DELETE FROM watches + WHERE watches.wid = OLD.wid; + + RETURN OLD; + END; +$$; + + CREATE TRIGGER dyads_insert_trigger INSTEAD OF INSERT ON dyads FOR EACH ROW @@ -531,9 +662,9 @@ CREATE TRIGGER dyads_insert_trigger CREATE TRIGGER dyads_update_trigger INSTEAD OF UPDATE ON dyads FOR EACH ROW - EXECUTE PROCEDURE _error_immutable_view(); + EXECUTE PROCEDURE dyads_update_func(); CREATE TRIGGER dyads_delete_trigger INSTEAD OF DELETE ON dyads FOR EACH ROW - EXECUTE PROCEDURE _error_immutable_view(); + EXECUTE PROCEDURE dyads_delete_func(); diff --git a/doc/src/views/dyads.m4 b/doc/src/views/dyads.m4 index 7f8a82c..cff1105 100644 --- a/doc/src/views/dyads.m4 +++ b/doc/src/views/dyads.m4 @@ -204,7 +204,31 @@ INSERT the values already existing in, or inserted into, the database. UPDATE - This operation is not allowed. + UPDATEing the DYADS view updates the underlying tables, as expected. DELETE - This operation is not allowed. + DELETEing rows from the DYADS view deletes rows from the underlying + tables. + + Rows are always deleted from |ROLES|, and may be deleted from + |EVENTS| and |WATCHES| as well. + + If, after deletion of the |ROLES| rows, there are no |ROLES| rows + related to the |EVENTS| row (that is, the |EVENTS| row which was + related to deleted |ROLES| rows), the |EVENTS| row is deleted. + + If, after deletion of the |EVENTS| row, there are no |EVENTS| rows + related to the |WATCHES| row (that is, the |WATCHES| row which was + related to the deleted |EVENTS| row), the |WATCHES| row is deleted. + + Note that |EVENTS| rows almost always have related rows on another + table, and an |EVENTS| row cannot be deleted if it has a related + row. + For example, pantgrunt events will have a related row on + |PANTGRUNTS|. + To delete a pantgrunt event, first delete the |PANTGRUNTS| row and + then delete the DYADS row. + + To make this process easier, there may be a view available. For + example, the |PANTGRUNTS_VIEW| deletes rows from the |PANTGRUNTS| + table before deleting rows from DYADS. -- 2.34.1